package com.caochenlei.codebuilder.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.caochenlei.db.meta.schema.Index;
import org.apache.commons.dbcp.BasicDataSource;

import com.caochenlei.codebuilder.config.KVConfigUtil;
import com.caochenlei.codebuilder.entity.Column;
import com.caochenlei.codebuilder.entity.Table;
import com.caochenlei.db.meta.core.MetaLoader;
import com.caochenlei.db.meta.core.MetaLoaderImpl;
import com.caochenlei.db.meta.schema.PrimaryKey;

public class DatabaseMetaDataUtil {

    private DatabaseMetaData databaseMetaData = null;
    private Connection connection = null;
    private Map<String, String> dbTypes = null;
    MetaLoader metaLoader = null;

    public DatabaseMetaDataUtil() {
        super();
    }

    public DatabaseMetaDataUtil(String dbDriverName, String dbUrl, String dbUser, String dbPassword) {
        // 读取数据库默认配置文件
        dbTypes = KVConfigUtil.read("converters");

        // 开启数据源连接（旧的方法）
        try {
            if (databaseMetaData == null) {
                Class.forName(dbDriverName);
                Properties props = new Properties();
                props.setProperty("user", dbUser);
                props.setProperty("password", dbPassword);
                props.setProperty("remarks", "true");
                props.setProperty("useInformationSchema", "true");
                connection = DriverManager.getConnection(dbUrl, props);
                databaseMetaData = connection.getMetaData();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // BasicDataSource进行数据源连接
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setUrl(dbUrl);
        dataSource.setUsername(dbUser);
        dataSource.setPassword(dbPassword);
        metaLoader = new MetaLoaderImpl(dataSource);
    }

    /**
     * 功能说明：获取所有的数据库
     *
     * @return
     */
    public List<String> getAllDatabases() {
        try {
            List<String> list = new ArrayList<String>();
            ResultSet rs = databaseMetaData.getCatalogs();
            while (rs.next()) {
                list.add(rs.getString(1));
            }
            rs.close();
            connection.close();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 功能说明：获得该用户下所有表
     *
     * @return
     */
    public List<Table> getAllTableList() {
        List<Table> list = new ArrayList<Table>();
        for (String tableName : metaLoader.getTableNames()) {
            // 解决SQL SERVER扫描出多余无效表的问问题
            if ("trace_xe_event_map".equals(tableName)) {
                continue;
            }
            if ("trace_xe_action_map".equals(tableName)) {
                continue;
            }
            // 获取表的对象
            com.caochenlei.db.meta.schema.Table rs = metaLoader.getTable(tableName);
            // 获取基本信息
            Table table = new Table();
            table.setTableName(rs.getName());
            table.setTableComment(rs.getComment());
            // 设置表的别名
            table.setTable1(Alias.getLower(table.getTableName()));
            table.setTable1Upper(Alias.getUpper(table.getTableName()));
            table.setTable2(Alias.getLowerAndCamel(table.getTableName()));
            table.setTable2Upper(Alias.getUpperAndCamel(table.getTableName()));
            table.setTable3(Alias.getLowerAndClearPrefixAndCamel(table.getTableName()));
            table.setTable3Upper(Alias.getUpperAndClearPrefixAndCamel(table.getTableName()));
            // 设置字段集合
            Map<String, List<Column>> columnsMap = getTableColumns(table.getTableName());
            table.setColumns(columnsMap.get(table.getTableName()));
            // 设置主键字段
            Column primaryKeyColumn = getPrimaryKeyColumn(table.getColumns());
            table.setTablePrimaryKeyName(primaryKeyColumn.getColumnName());
            table.setTablePrimaryKeyComment(primaryKeyColumn.getColumnComment());
            table.setTablePrimaryKeyType(primaryKeyColumn.getColumnType());
            table.setTablePrimaryKeyLanguageType(primaryKeyColumn.getColumnLanguageType());
            table.setTablePrimary1(Alias.getLower(primaryKeyColumn.getColumnName()));
            table.setTablePrimary1Upper(Alias.getUpper(primaryKeyColumn.getColumnName()));
            table.setTablePrimary2(Alias.getLowerAndCamel(primaryKeyColumn.getColumnName()));
            table.setTablePrimary2Upper(Alias.getUpperAndCamel(primaryKeyColumn.getColumnName()));
            table.setTablePrimary3(Alias.getLowerAndClearPrefixAndCamel(primaryKeyColumn.getColumnName()));
            table.setTablePrimary3Upper(Alias.getUpperAndClearPrefixAndCamel(primaryKeyColumn.getColumnName()));
            // 添加到列表中
            list.add(table);
        }
        return list;
    }

    /**
     * 功能说明：获得表的所有列信息
     *
     * @param tableName
     * @return
     */
    public Map<String, List<Column>> getTableColumns(String tableName) {
        Map<String, List<Column>> map = new LinkedHashMap<>();
        List<Column> columns = new ArrayList<Column>();

        // metax的table
        com.caochenlei.db.meta.schema.Table metaxTable = metaLoader.getTable(tableName);
        Map<String, com.caochenlei.db.meta.schema.Column> metaxColumns = metaxTable.getColumns();
        for (String columnName : metaxColumns.keySet()) {
            com.caochenlei.db.meta.schema.Column c = metaxColumns.get(columnName);
            Column column = new Column();// 获取列信息
            column.setColumnName(c.getName());// 列名称
            column.setColumnComment(c.getComment());// 列描述
            column.setColumnType(c.getTypeName().toUpperCase());// 列类型
            column.setIsPrimaryKey(false);// 列主键

            // 设置别名
            column.setColumn1(Alias.getLower(column.getColumnName()));
            column.setColumn1Upper(Alias.getUpper(column.getColumnName()));
            column.setColumn2(Alias.getLowerAndCamel(column.getColumnName()));
            column.setColumn2Upper(Alias.getUpperAndCamel(column.getColumnName()));
            column.setColumn3(Alias.getLowerAndClearPrefixAndCamel(column.getColumnName()));
            column.setColumn3Upper(Alias.getUpperAndClearPrefixAndCamel(column.getColumnName()));

            // 设置类型
            for (String dbType : dbTypes.keySet()) {
                if (dbType.equals(column.getColumnType())) {
                    column.setColumnLanguageType(dbTypes.get(dbType));
                    break;
                }
            }

            // 添加列表
            columns.add(column);
        }

        // 设置主键
        List<String> allPrimaryKeys = getAllPrimaryKeys(tableName);
        for (String primaryKeyStr : allPrimaryKeys) {
            for (int i = 0; i < columns.size(); i++) {
                if (columns.get(i).getColumnName().equals(primaryKeyStr)) {
                    columns.get(i).setIsPrimaryKey(true);
                }
            }
        }

        // 设置唯一键
        List<String> allUniqueKeys = getAllUniqueKeys(tableName);
        for (String uniqueKeyStr : allUniqueKeys) {
            for (int i = 0; i < columns.size(); i++) {
                if (columns.get(i).getColumnName().equals(uniqueKeyStr)) {
                    columns.get(i).setIsUniqueKey(true);
                }
            }
        }

        map.put(tableName, columns);
        return map;
    }

    /**
     * 功能说明：获得一个表的主键信息
     *
     * @param tableName
     * @return
     */
    public List<String> getAllPrimaryKeys(String tableName) {
        com.caochenlei.db.meta.schema.Table table = metaLoader.getTable(tableName);
        PrimaryKey primaryKey = table.getPrimaryKey();
        List<String> columns = primaryKey.getColumns();
        return columns;
    }

    /**
     * 功能说明：获得一个表的唯一键信息
     *
     * @param tableName
     * @return
     */
    public List<String> getAllUniqueKeys(String tableName) {
        com.caochenlei.db.meta.schema.Table table = metaLoader.getTable(tableName);
        Map<String, Index> indexs = table.getIndexs();
        List<String> columns = new ArrayList<>();
        Set<Map.Entry<String, Index>> entries = indexs.entrySet();
        for (Map.Entry<String, Index> entry : entries) {
            Index index = entry.getValue();
            columns.addAll(index.getColumnNames());
            continue;
        }
        return columns;
    }

    /**
     * 功能说明：获取当前表的唯一主键
     *
     * @param columns
     * @return
     */
    public static Column getPrimaryKeyColumn(List<Column> columns) {
        // 从字段列表查找第一个出现的主键信息
        for (Column column : columns) {
            if (column.getIsPrimaryKey()) {
                return column;
            }
        }
        // 这个表没有主键默认第一个字段为主键
        if (columns.size() > 0) {
            return columns.get(0);
        }
        // 说明主键和字段都不存在那就该报错了
        return null;
    }

}